const connection = require("../app/database")

class MomentService {
  async create(userId,content){
    const statement = `INSERT INTO moment (user_id,content) VALUES (?,?)`;
    const [result] = await connection.promise().execute(statement,[userId,content])

    return result
  }

  async getMomentById(momentId){
    const statement = `	
    SELECT 
    m.id as id,
    m.content as content,
    m.createAt as createTime,
    m.updateAt as updateTime,
    JSON_OBJECT('id',u.id,'name',u.name) as  author,
    (SELECT COUNT(*) FROM comment as c WHERE c.moment_id = m.id) as commentCount,
    IF(COUNT(c.id),JSON_ARRAYAGG(JSON_OBJECT('id',c.id,'content',c.content,'createTime',c.createAt,'user',JSON_OBJECT('id',cu.id,'name',cu.name))),JSON_ARRAY()) as commentList,
    (SELECT COUNT(*) FROM moment_label as ml WHERE ml.moment_id = m.id) as momentCount,
    IF(COUNT(ml.moment_id),JSON_ARRAYAGG(JSON_OBJECT('id',l.id,'name',l.name)),JSON_ARRAY())	as labels
    FROM moment as m 
    LEFT JOIN user as u ON m.user_id = u.id 
    LEFT JOIN comment as c ON c.moment_id = m.id
    LEFT JOIN user as cu ON cu.id = c.user_id
    LEFT JOIN moment_label as ml ON ml.moment_id = m.id
    LEFT JOIN label as l ON l.id = ml.label_id
	  WHERE m.id = ?
    GROUP BY m.id`;
    const [result] = await connection.promise().execute(statement,[momentId])
    return result
  }

  async getMomentList(offset,size){
  const statement = `
  SELECT 
  m.id as id,
  m.content as content,
  m.createAt as createTime,
  m.updateAt as updateTime,
  JSON_OBJECT('id',u.id,'name',u.name,'avatarUrl',u.avatar_url) as  author,
  (SELECT COUNT(*) FROM comment as c WHERE c.moment_id = m.id) as commentCount,
  IF(COUNT(c.id),JSON_ARRAYAGG(JSON_OBJECT('id',c.id,'content',c.content,'createTime',c.createAt,'user',JSON_OBJECT('id',cu.id,'name',cu.name,'avatarUrl',cu.avatar_url))),JSON_ARRAY()) as commentList,
  (SELECT COUNT(*) FROM moment_label as ml WHERE ml.moment_id = m.id) as momentCount,
  IF(COUNT(ml.moment_id),JSON_ARRAYAGG(JSON_OBJECT('id',l.id,'name',l.name)),JSON_ARRAY())	as labels,
  (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/',file.filename)) FROM file WHERE m.id = file.moment_id) as images
  FROM moment as m 
  LEFT JOIN user as u ON m.user_id = u.id 
  LEFT JOIN comment as c ON c.moment_id = m.id
  LEFT JOIN user as cu ON cu.id = c.user_id
  LEFT JOIN moment_label as ml ON ml.moment_id = m.id
  LEFT JOIN label as l ON l.id = ml.label_id
  GROUP BY m.id
  LIMIT ?,?
  `
    const [result] = await connection.promise().execute(statement,[offset,size])

    return result
  }

  async update(content,momentId){
    const statement = `
      UPDATE moment set content = ? where id = ?
    `
    const [result] = await connection.promise().execute(statement,[content,momentId])
    return result
  }
  
  async remove(momentId){
    const statement = `
      DELETE FROM moment WHERE id = ?
    `
    const [result] = await connection.promise().execute(statement,[momentId])
    return result
  }

  async hasLabelByComment(labelId,momentId){
    const statement = `
    SELECT * FROM moment_label WHERE label_id = ? && moment_id = ?
  `
    const [result] = await connection.promise().execute(statement,[labelId,momentId])
    return !!result[0]
  }

  async addLabel(labelId,momentId){
    const statement = `INSERT INTO moment_label (label_id,moment_id) VALUES (?,?)`
    const [result] = await connection.promise().execute(statement,[labelId,momentId])
    return result
  }
}

module.exports = new MomentService();